﻿using System;
using System.Collections;
using System.Collections.Concurrent;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Database.Core;
using Database.Core.Exceptions;
using Database.Sql.Attributes;

namespace Database.Sql
{
  /// <summary>
  ///   MS SQL stored procedure/query parameters.
  /// </summary>
  public class Parameters : IEnumerable<SqlParameter>, IDisposable
  {
    private List<SqlParameter> _parameters = new List<SqlParameter>();

    /// <summary>
    ///   Gets or sets the <see cref="SqlParameter" /> at the specified index.
    /// </summary>
    /// <param name="index">The index.</param>
    /// <returns>SqlParameter.</returns>
    /// <exception cref="IndexOutOfRangeException"></exception>
    public SqlParameter this[int index]
    {
      get { return _parameters[index]; }
      set
      {
        if(index >= _parameters.Count)
        {
          throw new IndexOutOfRangeException();
        }

        _parameters[index] = value;
      }
    }

    /// <summary>
    ///   Gets or sets the <see cref="SqlParameter" /> with the specified name.
    /// </summary>
    /// <param name="parameterName">Parameter name.</param>
    /// <returns>OdbcParameter.</returns>
    /// <exception cref="KeyNotFoundException"></exception>
    public SqlParameter this[string parameterName]
    {
      get { return _parameters.FirstOrDefault(t => t.ParameterName == parameterName); }
      set
      {
        var parameter = _parameters.FirstOrDefault(t => t.ParameterName == parameterName);

        if(parameter == null)
        {
          throw new KeyNotFoundException($"'{parameterName}' does not exist in parameter collection");
        }

        parameter.Value = value;
      }
    }

    /// <summary>
    ///   Returns the number of parameters in the list.
    /// </summary>
    public int Count
    {
      get { return _parameters.Count; }
    }

    /// <summary>
    ///   Initializes a new instance of the <see cref="Parameters" /> class.
    /// </summary>
    public Parameters() {}

    /// <summary>
    ///   Initializes a new instance of the <see cref="Parameters" /> class.
    /// </summary>
    /// <param name="parameterName">Name of the parameter.</param>
    /// <param name="value">The value.</param>
    public Parameters(string parameterName, object value)
    {
      Add(parameterName, value);
    }

    /// <summary>
    ///   Initializes a new instance of the <see cref="Parameters" /> class.
    /// </summary>
    /// <param name="parameterName">Name of the parameter.</param>
    /// <param name="value">The value.</param>
    /// <param name="direction">SQL <see cref="ParameterDirection" /></param>
    public Parameters(string parameterName, object value, ParameterDirection direction)
    {
      Add(parameterName, value, direction);
    }

    /// <summary>
    ///   XML string from list of values.
    /// </summary>
    /// <typeparam name="T">Type of values</typeparam>
    /// <param name="xmlRootName">root name item</param>
    /// <param name="item">identity of item</param>
    /// <param name="values">List of values</param>
    /// <returns>Returns a string in XML format to utilize in SQL.</returns>
    public static string XmlString<T>(string xmlRootName, string item, List<T> values)
    {
      if(!typeof(T).IsSqlConvertableType())
      {
        return null;
      }

      var stringBuilder = new StringBuilder();

      stringBuilder.Append($"<{xmlRootName}>");

      foreach(var t in values)
      {
        stringBuilder.Append($"<{item}>{t}</{item}>");
      }

      stringBuilder.AppendFormat("</{0}>", xmlRootName);

      return stringBuilder.ToString();
    }

    /// <summary>
    ///   Adds the given parameter to the list of parameters
    /// </summary>
    /// <param name="parameter">Parameter to add</param>
    /// <returns>
    ///   True if the parameter was added. False if a parameter with the same name already
    ///   existed in the parameter list
    /// </returns>
    public bool Add(SqlParameter parameter)
    {
      if(this[parameter.ParameterName] != null)
      {
        return false;
      }

      _parameters.Add(parameter);

      return true;
    }

    /// <summary>
    ///   Creates a new parameter with the given name and value then adds it to
    ///   the list of parameters.
    /// </summary>
    /// <param name="parameterName">Name of the parameter</param>
    /// <param name="value">Value of the parameter</param>
    /// <returns>
    ///   True if the parameter was added. False if a parameter with the same name already
    ///   existed in the parameter list
    /// </returns>
    public bool Add(string parameterName, object value)
    {
      if(this[parameterName] != null)
      {
        return false;
      }

      var valueType = value.GetType().GetUnderlyingType();

      if(valueType == typeof(Enum)
         && ((Enum)value).IsStringDefined())
      {
        value = ((Enum)value).GetEnumStringValue();
      }

      var underlyingValue = Convert.ChangeType(value, valueType);

      _parameters.Add(new SqlParameter(parameterName, underlyingValue));

      return true;
    }

    /// <summary>
    ///   Creates a new parameter with the given name and value then adds it to
    ///   the list of parameters.
    /// </summary>
    /// <param name="parameterName">Name of the parameter</param>
    /// <param name="value">Value of the parameter</param>
    /// <param name="direction">SQL <see cref="ParameterDirection" /></param>
    /// <returns>
    ///   True if the parameter was added. False if a parameter with the same name already
    ///   existed in the parameter list
    /// </returns>
    public bool Add(string parameterName, object value, ParameterDirection direction)
    {
      if(this[parameterName] != null)
      {
        return false;
      }

      var valueType = value.GetType().GetUnderlyingType();

      if(valueType == typeof(Enum)
         && ((Enum)value).IsStringDefined())
      {
        value = ((Enum)value).GetEnumStringValue();
      }

      var underlyingValue = Convert.ChangeType(value, valueType);

      var sqlParam = new SqlParameter(parameterName, underlyingValue);

      if(direction != ParameterDirection.Input)
      {
        sqlParam.Direction = direction;
      }

      _parameters.Add(sqlParam);

      return true;
    }

    /// <summary>
    ///   Adds a table valued parameter of the specified type
    /// </summary>
    /// <remarks>
    ///   If a complex type (class or struct) is passed in, the output columns will be in the order they are defined.
    ///   Create your SQL User Defined Table Type accordingly.
    /// </remarks>
    /// <typeparam name="T">Type contained in collection</typeparam>
    /// <param name="parameterName">Name of the parameter.</param>
    /// <param name="values">Collection of values.</param>
    /// <param name="typeName">Table valued parameter name (eg. dbo.SimpleValueType).</param>
    /// <returns><c>true</c> if parameter was added, <c>false</c> otherwise.</returns>
    public bool Add<T>(string parameterName, IList<T> values, string typeName)
    {
      if(!values.Any())
      {
        return false;
      }

      if(!typeName.StartsWith("dbo."))
      {
        typeName = $"dbo.{typeName}";
      }

      return typeof(T).IsSqlConvertableType()
        ? AddSimpleTVP(parameterName, values, typeName)
        : AddComplexTVP(parameterName, values, typeName);
    }

    /// <summary>
    ///   Adds a table valued parameter of the specified type
    /// </summary>
    /// <remarks>
    ///   If a complex type (class or struct) is passed in, the output columns will be in the order they are defined.
    ///   Create your SQL User Defined Table Type accordingly.
    /// </remarks>
    /// <param name="parameterName">Name of the parameter.</param>
    /// <param name="values">Collection of integers.</param>
    /// <param name="typeName">Table valued parameter name (eg. dbo.SimpleValueType).</param>
    /// <returns><c>true</c> if parameter was added, <c>false</c> otherwise.</returns>
    public bool Add(string parameterName, IList<int> values, string typeName)
    {
      if(!values.Any())
      {
        return false;
      }

      if(!typeName.StartsWith("dbo."))
      {
        typeName = $"dbo.{typeName}";
      }

      return AddSimpleIntTVP(parameterName, values, typeName);
    }

    /// <summary>
    ///   Creates a new table valued parameter with the given name and type from the given <see cref="DataTable" />
    /// </summary>
    /// <param name="parameterName">Name of the parameter.</param>
    /// <param name="table">The table.</param>
    /// <param name="typeName">Name of the type.</param>
    /// <returns><c>true</c> if the parameter was added, <c>false</c> otherwise.</returns>
    public bool Add(string parameterName, DataTable table, string typeName)
    {
      var sqlParameter = new SqlParameter(parameterName, table)
      {
        SqlDbType = SqlDbType.Structured,
        TypeName = typeName
      };

      _parameters.Add(sqlParameter);
      return true;
    }

    /// <summary>
    ///   Creates a new parameter with the given name, database type, and value
    ///   then adds it to the list of parameters.
    /// </summary>
    /// <param name="parameterName">Name of the parameter</param>
    /// <param name="type">Type of the parameter</param>
    /// <param name="value">Value of the parameter</param>
    /// <returns>
    ///   True if the parameter was added. False if a parameter with the same name already
    ///   existed in the parameter list
    /// </returns>
    public bool Add(string parameterName, SqlDbType type, object value)
    {
      if(this[parameterName] != null)
      {
        return false;
      }

      var sqlParameter = new SqlParameter(parameterName, value)
      {
        SqlDbType = type
      };

      _parameters.Add(sqlParameter);

      return true;
    }

    /// <summary>
    ///   Creates a new parameter with the given name, database type, and value
    ///   then adds it to the list of parameters.
    /// </summary>
    /// <param name="parameterName">Name of the parameter</param>
    /// <param name="type">Type of the parameter</param>
    /// <param name="value">Value of the parameter</param>
    /// <param name="direction">SQL <see cref="ParameterDirection" /></param>
    /// <returns>
    ///   True if the parameter was added. False if a parameter with the same name already
    ///   existed in the parameter list
    /// </returns>
    public bool Add(string parameterName, SqlDbType type, object value, ParameterDirection direction)
    {
      if(this[parameterName] != null)
      {
        return false;
      }

      var sqlParameter = new SqlParameter(parameterName, value)
      {
        SqlDbType = type,
        Direction = direction
      };

      _parameters.Add(sqlParameter);

      return true;
    }

    /// <summary>
    ///   Creates a new table valued parameter (primitive or SQL mappable type (eg. int, string, DateTime, Guid))
    ///   with the given name, <see cref="IEnumerable{T}" /> value, and SQL table valued type
    ///   then adds it to the list of parameters.
    /// </summary>
    /// <param name="parameterName">Parameter name.</param>
    /// <param name="value">Enumerable value.</param>
    /// <param name="typeName">SQL Table Valued Parameter Type</param>
    /// <returns><c>true</c> if item has been added, <c>false</c> otherwise.</returns>
    public bool AddSimpleIntTVP(
      string parameterName, IEnumerable<int> value, string typeName)
    {
      if(this[parameterName] != null)
      {
        return false;
      }

      var table = new DataTable();

      table.Columns.Add("value", typeof(int));

      foreach(var item in value)
      {
        table.Rows.Add(item);
      }

      var sqlParameter = new SqlParameter(parameterName, table)
      {
        SqlDbType = SqlDbType.Structured,
        TypeName = typeName
      };

      _parameters.Add(sqlParameter);

      return true;
    }

    /// <summary>
    ///   Creates a new table valued parameter (primitive or SQL mappable type (eg. int, string, DateTime, Guid))
    ///   with the given name, <see cref="IEnumerable{T}" /> value, and SQL table valued type
    ///   then adds it to the list of parameters.
    /// </summary>
    /// <typeparam name="T">Enumerable type</typeparam>
    /// <param name="parameterName">Parameter name.</param>
    /// <param name="value">Enumerable value.</param>
    /// <param name="typeName">SQL Table Valued Parameter Type</param>
    /// <returns><c>true</c> if item has been added, <c>false</c> otherwise.</returns>
    public bool AddSimpleTVP<T>(
      string parameterName, IEnumerable<T> value, string typeName)
    {
      if(this[parameterName] != null)
      {
        return false;
      }

      if(!typeof(T).IsSqlConvertableType())
      {
        throw new ArgumentException(
          "value parameter must be an IEnumerable<T> of a primitive or SQL mappable type (eg. int, string, DateTime, Guid).",
          nameof(value));
      }

      var table = new DataTable();

      table.Columns.Add("value", typeof(string));

      foreach(var item in value)
      {
        table.Rows.Add(item.ToString().Trim());
      }

      var sqlParameter = new SqlParameter(parameterName, table)
      {
        SqlDbType = SqlDbType.Structured,
        TypeName = typeName
      };

      _parameters.Add(sqlParameter);
      return true;
    }

    /// <summary>
    ///   Creates a new table valued parameter (containing a complex type that cannot be converted directly to SQL type)
    ///   with the given name, <see cref="IList{T}" /> value, and SQL table valued type
    ///   then adds it to the list of parameters.
    /// </summary>
    /// <typeparam name="T">Enumerable type</typeparam>
    /// <param name="parameterName">Parameter name.</param>
    /// <param name="value">Enumerable value.</param>
    /// <param name="typeName">SQL Table Valued Parameter Type</param>
    /// <returns><c>true</c> if item has been added, <c>false</c> otherwise.</returns>
    public bool AddComplexTVP<T>(
      string parameterName, IList<T> value,
      string typeName)
    {
      if(this[parameterName] != null)
      {
        return false;
      }

      if(typeof(T).IsSqlConvertableType())
      {
        throw new ArgumentException(
          "value parameter must be an IEnumerable<T> of a complex type (eg. object consisting of multiple properties).",
          nameof(value));
      }

      if(typeof(IDynamicMetaObjectProvider).IsAssignableFrom(typeof(T)))
      {
        throw new ArgumentException(
          "value parameter cannot contain dynamic objects... yet.",
          nameof(value));
      }

      return CreateComplexTVP(parameterName, value, typeName);
    }

    /// <summary>
    ///   Removes a parameter with the provided name from the parameter list.
    /// </summary>
    /// <param name="parameterName">Name of the parameter to remove.</param>
    /// <returns>
    ///   True if the parameter was found and removed. Otherwise, returns false.
    /// </returns>
    public bool Remove(string parameterName)
    {
      var parameter = this[parameterName];

      if(parameter == null)
      {
        return false;
      }

      _parameters.Remove(parameter);

      return true;
    }

    /// <summary>
    ///   Removes the parameter at the provided index.
    /// </summary>
    /// <param name="index">Index of the parameter to remove</param>
    public void RemoveAt(int index)
    {
      _parameters.RemoveAt(index);
    }

    /// <summary>
    ///   Clear all parameters from the parameter list.
    /// </summary>
    public void Clear()
    {
      _parameters.Clear();
    }

    /// <summary>
    ///   Retrieves index of the specified parameter, by name.
    /// </summary>
    /// <param name="parameterName">Name of the parameter to find.</param>
    /// <returns>
    ///   If found, returns the value of the index (positive integer). Otherwise, -1 is returned.
    /// </returns>
    public int IndexOf(string parameterName)
    {
      for(var index = 0; index < _parameters.Count; ++index)
      {
        if(_parameters[index].ParameterName == parameterName)
        {
          return index;
        }
      }

      return -1;
    }

    /// <summary>
    ///   Returns an enumerator that iterates through the collection.
    /// </summary>
    /// <returns>
    ///   A <see cref="T:System.Collections.Generic.IEnumerator`1" /> that can be used to iterate through the
    ///   collection.
    /// </returns>
    public IEnumerator<SqlParameter> GetEnumerator()
    {
      return _parameters.GetEnumerator();
    }

    IEnumerator IEnumerable.GetEnumerator()
    {
      return _parameters.GetEnumerator();
    }

    /// <summary>
    ///   Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources.
    /// </summary>
    public void Dispose()
    {
      _parameters = null;
    }

    private bool CreateComplexTVP<T>(string parameterName, IEnumerable<T> value, string typeName)
    {
      var properties = typeof(T).GetProperties()
        .Where(p => p.GetCustomAttributes(typeof(TVPIgnore), false).Length < 1)
        .OrderBy(p => p.MetadataToken)
        .ToList();

      var table = new DataTable();
      var selectedProps = new ConcurrentBag<PropertyInfo>();
      var columns = new ConcurrentDictionary<int, DataColumn>();

      var cancelTokenSource = new CancellationTokenSource();

      var options = new ParallelOptions
      {
        CancellationToken = cancelTokenSource.Token,
        MaxDegreeOfParallelism = 2
      };

      Parallel.For(0, properties.Count(), options, i =>
      {
        try
        {
          var propertyType = properties[i].PropertyType;
          var sqlType = (properties[i].GetCustomAttribute(typeof(TVPSqlType), true) as TVPSqlType)?.SqlType;
          var currentType = sqlType ?? propertyType;
          var hasSqlType = sqlType != null;
          var allowDbNull = false;

          if(!hasSqlType
             && currentType.IsGenericType
             && currentType.GetGenericTypeDefinition() == typeof(Nullable<>))
          {
            currentType = Nullable.GetUnderlyingType(propertyType);

            allowDbNull = true;
          }

          if(!currentType.IsSqlConvertableType())
          {
            throw new TypeConversionException(properties[i],
              $"Exception during TVP column type conversion. Type: {currentType}", null);
          }

          columns.TryAdd(i, new DataColumn(properties[i].Name, currentType)
          {
            AllowDBNull = allowDbNull
          });

          selectedProps.Add(properties[i]);
        }
        catch(Exception ex)
        {
          throw new TypeConversionException(properties[i],
            $"Exception during TVP column type conversion. Type: {typeName}", ex);
        }
      });

      table.Columns.AddRange(columns.OrderBy(c => c.Key).Select(c => c.Value).ToArray());

      foreach(var item in value)
      {
        var row = table.NewRow();

        foreach(var prop in selectedProps)
        {
          var colName = prop.Name;

          var propValue = prop.GetValue(item, null);

          if(prop.PropertyType.IsEnum
             && propValue != null
             && ((Enum)propValue).IsStringDefined())
          {
            //TODO: Need a way to select either StringValueAttribute, Enum.GetValue, or DescriptionAttribute
            propValue = ((Enum)propValue).GetEnumStringValue();
          }

          row[colName] = propValue ?? DBNull.Value;
        }

        table.Rows.Add(row);
      }

      var sqlParameter = new SqlParameter(parameterName, table)
      {
        SqlDbType = SqlDbType.Structured,
        TypeName = typeName
      };

      _parameters.Add(sqlParameter);

      return true;
    }

    //TODO: Need to find a way to create a complex TVP from a dynamic object.
    private bool CreateComplexTVPFromExpando<T>(
      string parameterName, IList<T> value,
      string typeName)
    {
      var table = new DataTable();

      table.Columns.AddRange(value
        .Cast<IDictionary<string, object>>()
        .First()
        .Select(v => v.Value != null
          ? new DataColumn(v.Key, v.Value.GetType().GetUnderlyingType())
          : new DataColumn(v.Key)).ToArray());

      foreach(var item in value)
      {
        var row = table.NewRow();

        foreach(var prop in (IDictionary<string, object>)item)
        {
          row[prop.Key] = prop.Value ?? DBNull.Value;
        }

        table.Rows.Add(row);
      }

      var sqlParameter = new SqlParameter(parameterName, table)
      {
        SqlDbType = SqlDbType.Structured,
        TypeName = typeName
      };

      _parameters.Add(sqlParameter);

      return true;
    }
  }
}
